# -*- Coding = utf-8 -*-
# @time: 2021/1/31 13:23
# Author: YKL

import MySQLdb as mysql


# 1.创建mysql数据库连接,获取连接对象
conn = mysql.connect(host='39.105.56.50', port=3306,
                     user='root', password='Yukl.911',
                     database='contacts', charset='utf8mb4',
                     connect_timeout=3)
try:
    print('联系人管理:\n1.新建联系人\n2.删除联系人\n3.查找联系人\n4.修改联系人')
    m = int(input('你想干嘛?'))
    # 2.基于连接对象创建游标对象
    with conn.cursor() as cursor:

        if m == 1:
            # 3.通过游标对象向数据库发出SQL语句,并获取执行结果
            peid = int(input('编号:'))
            pename = input('名字:')
            petel = input('电话:')
            affected_rows = cursor.execute(
                'insert into tb_person values (%s, %s, %s)',
                (pename, petel, peid)
            )
            if affected_rows == 1:
                print('新增联系人成功')

            # 4.操作成功:通过连接对象执行提交操作(让之前的操作生效)
                conn.commit()
        elif m == 2:
            peid = int(input('编号:'))

            affected_rows = cursor.execute(
                'delete from tb_person where peid=%s',
                (peid,)
            )
            if affected_rows == 1:
                print('删除联系人成功')

                conn.commit()
        elif m == 3:
            pename = input('名字:')
            affected_rows = cursor.execute(
                'select * from tb_person where pename=%s',
                (pename,)
            )
            for row in cursor.fetchall():
                print(row)
        elif m == 4:
            peid = int(input('请输入需要修改的id:'))
            pename = input('名字:')
            petel = input('电话:')
            affected_rows = cursor.execute(
                'update tb_person  set pename=%s,petel=%s where peid=%s',
                (pename,petel,peid,)
            )
            if affected_rows == 1:
                print('修改联系人成功')

                conn.commit()

except mysql.MySQLError as err:
    print(err)
    # 操作失败:通过连接对象执行回滚操作(撤销之前的操作)
    conn.rollback()
finally:
    # 5.关闭连接,释放资源
    conn.close()